﻿using Common;
using System.Collections;
using System.Data.SqlClient;

namespace yongGu
{
    public class DataAccess
    {
        /*
            销售出库单：  T_SAL_OUTSTOCKENTRY,T_SAL_OUTSTOCK        F_UIVG_TEXT_RE5    FBILLNO    1
            其他出库单：  T_STK_MISDELIVERYENTRY,T_STK_MISDELIVERY  F_UIVG_TEXT_QTR    FBILLNO    2
            生产领料单：  T_PRD_PICKMTRLDATA,T_PRD_PICKMTRL         F_UIVG_TEXT_APV    FBILLNO    3
            生产入库单：  T_PRD_INSTOCKENTRY                        F_UIVG_TEXT_TZK    FEntryId    4
            其他入库单：  T_STK_MISCELLANEOUSENTRY                  F_UIVG_TEXT_83G    FEntryId    5
        */
        public DataAccess()
        {
            hs.Add(1, new string[] { "T_SAL_OUTSTOCKENTRY", "T_SAL_OUTSTOCK", "F_UIVG_TEXT_RE5" });
            hs.Add(2, new string[] { "T_STK_MISDELIVERYENTRY", "T_STK_MISDELIVERY", "F_UIVG_TEXT_QTR" });
            hs.Add(3, new string[] { "T_PRD_PICKMTRLDATA", "T_PRD_PICKMTRL", "F_UIVG_TEXT_APV" });
            hs.Add(4, new string[] { "T_PRD_INSTOCKENTRY", "T_PRD_INSTOCK", "F_UIVG_TEXT_TZK" });
            hs.Add(5, new string[] { "T_STK_MISCELLANEOUSENTRY", "T_STK_MISCELLANEOUS", "F_UIVG_TEXT_83G" });
            
            hs.Add(6, new string[] { "T_STK_STKTRANSFERINENTRY", "T_STK_STKTRANSFERIN", "F_TBKE_YHRBS" });
            hs.Add(7, new string[] { "T_STK_STKTRANSFERINENTRY", "T_STK_STKTRANSFERIN", "F_TBKE_YHRBS" });

        }
        public List<MaterialModel> QueryMaterial(string startTime)
        {
            string SQL = "SELECT * FROM V_UDF_MATERIAL WHERE 1 = 1 AND updateTime >= @P"; //改用视图
            Config.SqlLogger.Debug("查询物料准备同步:" + SQL + "\r\n" + "@P=" + startTime);
            List<MaterialModel> result = new List<MaterialModel>();
            using (SqlConnection sqlconn = new SqlConnection())
            {
                sqlconn.ConnectionString = Config.SqlConn;
                sqlconn.Open();
                SqlCommand sqlCommand = new SqlCommand(SQL, sqlconn);
                sqlCommand.Parameters.AddWithValue("@P", startTime);
                SqlDataReader reader = sqlCommand.ExecuteReader();
                while (reader.Read())
                {
                    MaterialModel material = new MaterialModel();
                    material.remark = (string)reader["remark"];
                    material.skuName = (string)reader["skuName"];
                    material.skuCode = (string)reader["skuCode"];
                    material.updateTime = ((DateTime)reader["updateTime"]).ToString("yyyy-MM-dd HH:mm:ss");
                    material.unit = (string)reader["unit"];
                    material.productCode = (string)reader["productCode"];
                    material.skuBand = (string)reader["skuBand"];
                    material.colour = (string)reader["colour"];

                    result.Add(material);
                }
                sqlconn.Close();
            }
            return result;
        }

        Hashtable hs = new Hashtable();

        public List<StockOutModel> GetStockOut()
        {
            string SQL = "SELECT * FROM V_UDF_FINDSTOCKOUT4WMS "; //改用视图
                                                                  //string SQL_UPDATE = "INSERT INTO UDF_STOCKOUT_RECORD(BILLCODE,MAKETIME)VALUES(@P,GETDATE())";
            Config.SqlLogger.Debug("查询可出库的记录：" + SQL);
            List<StockOutModel> result = new List<StockOutModel>();
            using (SqlConnection sqlconn = new SqlConnection())
            {
                sqlconn.ConnectionString = Config.SqlConn;
                sqlconn.Open();
                SqlCommand sqlCommand = new SqlCommand(SQL, sqlconn);
                using (SqlDataReader reader = sqlCommand.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        StockOutModel stockOut = new StockOutModel();
                        stockOut.FRealQty = (decimal)reader["FRealQty"];
                        stockOut.FBillNo = (string)reader["FBillNo"];
                        stockOut.FName = (string)reader["FName"];
                        stockOut.FNumber = (string)reader["FNumber"];
                        stockOut.Address = (string)reader["FBillNo"];
                        stockOut.Type = (int)reader["Type"];
                        result.Add(stockOut);
                    }
                }
                sqlCommand.Dispose();
                foreach (var item in result)
                {
                    string[] v = (string[])(hs[item.Type]);
                    string updateSQL = String.Format("UPDATE {0} SET {1} = '传输中' WHERE FID IN (SELECT FID FROM {2} WHERE FBILLNO = @P) ",
                        v[0], v[2], v[1]);
                    Config.Logger.Debug("为提交出库申请的记录打上标记：" + updateSQL + "\r\n@P=" + item.FBillNo);
                    using (SqlCommand sqlCommandUpdate = new SqlCommand(updateSQL, sqlconn))
                    {
                        sqlCommandUpdate.Parameters.AddWithValue("@P", item.FBillNo);
                        var i = sqlCommandUpdate.ExecuteNonQuery();
                    }
                }
                sqlconn.Close();
            }
            
            return result;
        }
        public List<StockInModel> GetStockIn()
        {
            string SQL = "SELECT TOP 5 * FROM V_UDF_FINDSTOCK4WMS WHERE 1 = 1 "; //改用视图
            Config.SqlLogger.Debug("查询可入库的记录：" + SQL);

            List<StockInModel> result = new List<StockInModel>();
            using (SqlConnection sqlconn = new SqlConnection())
            {
                sqlconn.ConnectionString = Config.SqlConn;
                sqlconn.Open();
                SqlCommand sqlCommand = new SqlCommand(SQL, sqlconn);
                SqlDataReader reader = sqlCommand.ExecuteReader();
                while (reader.Read())
                {
                    StockInModel stockIn = new StockInModel();
                    stockIn.FRealQty = (decimal)reader["FRealQty"];
                    stockIn.FBillNo = (string)reader["FBillNo"];
                    stockIn.FDate = (DateTime)reader["FDate"];
                    stockIn.FName = (string)reader["FName"];
                    stockIn.FNumber = (string)reader["FNumber"];
                    stockIn.FSeq = (int)reader["FSeq"];
                    stockIn.FEntryId = (int)reader["FEntryId"];
                    stockIn.FSrcBillNo = (string)reader["FSrcBillNo"];
                    stockIn.StockLoc = (string)reader["StockLoc"];
                    stockIn.Type = (Int32)reader["Type"];
                    result.Add(stockIn);
                    using (SqlConnection sqlconn2 = new SqlConnection())
                    {
                        sqlconn2.ConnectionString = Config.SqlConn;
                        sqlconn2.Open();
                        foreach (var item in result)
                        {
                            string[] v = (string[])(hs[stockIn.Type]);
                            string updateSQL = String.Format("UPDATE {0} SET {1} = '传输中' WHERE FENTRYID = @P1",
                                v[0], v[2]);
                            Config.SqlLogger.Debug("为已提交入库的记录打上标记："+updateSQL + "\r\n@P1=" + item.FEntryId);

                            using (SqlCommand sqlCommandUpdate = new SqlCommand(updateSQL, sqlconn2))
                            {
                                sqlCommandUpdate.Parameters.AddWithValue("@P1", item.FEntryId);
                                var i = sqlCommandUpdate.ExecuteNonQuery();

                            }
                        }
                        sqlconn2.Close();
                    }
                }
                sqlconn.Close();
            }

            return result;
        }
        public void SetInResult(int id, string msg, int type)
        {
            //string SQL = "UPDATE UDF_WMS_STORKIN_RECORD SET RESULT = @P1,MESSAGE = @P2 WHERE RECORDID = @P3 AND TYPE = @P4";
            string[] v = (string[])(hs[type]);
            string updateSQL = String.Format("UPDATE {0} SET {1} = @P1 WHERE FENTRYID = @P2",
                v[0], v[2]);
            Config.SqlLogger.Debug("更新WMS返回的入库凭据："+updateSQL + "\r\n@P1=" + msg + "@P2=\r\n" + id);

            using (SqlConnection sqlconn = new SqlConnection())
            {
                sqlconn.ConnectionString = Config.SqlConn;
                sqlconn.Open();
                using (SqlCommand sqlCommandUpdate = new SqlCommand(updateSQL, sqlconn))
                {
                    sqlCommandUpdate.Parameters.AddWithValue("@P1", msg);
                    sqlCommandUpdate.Parameters.AddWithValue("@P2", id);
                    var i = sqlCommandUpdate.ExecuteNonQuery();

                }
                sqlconn.Close();
            }
        }
        
        public void SetInInfo(string billCode, string materialCode, string msg)
        {
            var billcodeArr = billCode.Split("_");
            if (billcodeArr.Length != 2) return;
            int seq = int.Parse(billcodeArr[1]);
            string[] sql4 = hs[4] as string[];
            string[] sql5 = hs[5] as string[];
            string[] sql6 = hs[7] as string[];
            string SQL_FORMAT = "UPDATE {0} SET {1} = @P1 WHERE FSEQ = @P2 AND FID = (SELECT FID FROM {2} WHERE FBILLNO = @P3)";
            string[] SQLs = {
                String.Format(SQL_FORMAT,sql4[0],sql4[2],sql4[1]),
                String.Format(SQL_FORMAT,sql5[0],sql5[2],sql5[1]),
                String.Format(SQL_FORMAT,sql6[0],sql6[2],sql6[1])
            };
            string[] TABLEs = {
                sql4[0],
                sql5[0],
                sql6[0]
            };

            var i = 0;
            for (int ic = 0; ic < SQLs.Length; ic++)
            {
                using (SqlConnection sqlconn = new SqlConnection())
                {
                    Config.SqlLogger.Debug("更新WMS的入库执行反馈结果：" + SQLs[ic] + "\r\n@P1=" + msg + "@P2=\r\n" + seq + "@P3=\r\n" + billcodeArr[0]);
                    sqlconn.ConnectionString = Config.SqlConn;
                    sqlconn.Open();
                    using (SqlCommand sqlCommandUpdate = new SqlCommand(SQLs[ic], sqlconn))
                    {
                        sqlCommandUpdate.Parameters.AddWithValue("@P1", msg);
                        sqlCommandUpdate.Parameters.AddWithValue("@P2", seq);
                        sqlCommandUpdate.Parameters.AddWithValue("@P3", billcodeArr[0]);
                        i = sqlCommandUpdate.ExecuteNonQuery();
                    }
                    sqlconn.Close();
                }
                if (i > 0) return;
                else Config.SqlLogger.Debug("在表"+TABLEs[i]+"中没有找到目标单据，尝试在下一个表中寻找");
            }
            
            if (i == 0)
            {
                Config.Logger.Debug($"无法根据{billCode}找到对应的原始订单，无法回写状态");
            }
        }
        public void SetOutResult(string bill, int code, string msg, int type)
        {
            string[] v = (string[])(hs[type]);
            string updateSQL = String.Format("UPDATE {0} SET {1} = @P2 WHERE FID IN (SELECT FID FROM {2} WHERE FBILLNO = @P3)",
                v[0], v[2], v[1]);
            Config.SqlLogger.Debug("更新WMS返回的出库凭据：" + updateSQL + "@P2=\r\n" + code.ToString() + "-" + msg + "@P3=\r\n" + bill);
            using (SqlConnection sqlconn = new SqlConnection())
            {
                sqlconn.ConnectionString = Config.SqlConn;
                sqlconn.Open();
                using (SqlCommand sqlCommandUpdate = new SqlCommand(updateSQL, sqlconn))
                {
                    sqlCommandUpdate.Parameters.AddWithValue("@P2", code.ToString()+"-"+msg);
                    sqlCommandUpdate.Parameters.AddWithValue("@P3", bill);
                    var i = sqlCommandUpdate.ExecuteNonQuery();
                }
            }
        }

        public void SetOutInfo(string billCode, string materialCode, string msg)
        {
            string[] sql1 = hs[1] as string[] ;
            string[] sql2 = hs[2] as string[];
            string[] sql3 = hs[3] as string[];
            string[] sql7 = hs[7] as string[];
            string[] TABLEs = {
                sql1[0],
                sql2[0],
                sql3[0],
                sql7[0],
            };
            string SQL_FORMAT = "UPDATE {0} SET {2} = @P1 WHERE FMATERIALID IN (SELECT FMATERIALID FROM T_BD_MATERIAL WHERE FNUMBER = @P2) AND FID = (SELECT FID FROM {1} WHERE FBILLNO = @P3)";
            string[] SQLS = {
                String.Format(SQL_FORMAT, sql1[0], sql1[1], sql1[2]),
                String.Format(SQL_FORMAT, sql2[0], sql2[1], sql2[2]),
                String.Format(SQL_FORMAT, sql3[0], sql3[1], sql3[2]),
                String.Format(SQL_FORMAT, sql7[0], sql7[1], sql7[2]),
            };
            var i = 0;
            for (int ic = 0; ic < SQLS.Length; ic++)
            {
                using (SqlConnection sqlconn = new SqlConnection())
                {
                    Config.SqlLogger.Debug("更新WMS的出库执行反馈结果：" + SQLS[ic] + "\r\n@P1=" + msg + "@P2=\r\n" + materialCode + "@P3=\r\n" + billCode);
                    sqlconn.ConnectionString = Config.SqlConn;
                    sqlconn.Open();
                    using (SqlCommand sqlCommandUpdate = new SqlCommand(SQLS[ic], sqlconn))
                    {
                        sqlCommandUpdate.Parameters.AddWithValue("@P1", msg);
                        sqlCommandUpdate.Parameters.AddWithValue("@P2", materialCode);
                        sqlCommandUpdate.Parameters.AddWithValue("@P3", billCode);
                        i = sqlCommandUpdate.ExecuteNonQuery();
                    }
                    sqlconn.Close();
                }
                if (i > 0) return;
                else Config.SqlLogger.Debug("在表"+TABLEs[i]+"中没有找到目标单据，尝试在下一个表中寻找");
            }
            if (i == 0)
            {
                Config.Logger.Debug($"无法根据{billCode}找到对应的原始订单，无法回写状态");
            }
        }


        
    }
}
